********************************************************************************
** 	TITLE:		UK83_results                                                  ** 
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       1918-2019election_results_by_pcon         		              **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Open log file

capture log close
log using "UK83_results.smcl", replace

* Import the dataset

import excel "1918-2019election_results_by_pcon.xlsx", cellrange(B5:AP654) sheet(1983) clear

* Reshape

drop G J M P S V Y AB AE AH AK AN

rename B id
rename C constituency_name
rename D county
rename E region
rename F electorate
rename H votes1
rename I share1
rename K votes2
rename L share2
rename N votes3
rename O share3
rename Q votes4
rename R share4
rename T votes5
rename U share5
rename W votes6
rename X share6
rename Z votes7
rename AA share7
rename AC votes8
rename AD share8
rename AF votes9
rename AG share9
rename AI votes10
rename AJ share10
rename AL votes11
rename AM share11
rename AO totalvotes
rename AP turnout

reshape long votes share, i(id) j(party_code)  

gen party_name = " "

replace party_name = "Conservative" if party_code == 1 
replace party_name = "Labour" if party_code == 2
replace party_name = "Alliance" if party_code == 3
replace party_name = "SNP/Plaid Cymru" if party_code == 4
replace party_name = "DUP" if party_code == 5 
replace party_name = "OUP" if party_code == 6
replace party_name = "Sinn Fein" if party_code == 7
replace party_name = "SDLP" if party_code == 8
replace party_name = "Alliance (Northern Ireland)" if party_code == 9
replace party_name = "Workers' Party" if party_code == 10
replace party_name = "Other" if party_code == 11

drop party_code

gen party_code = .

replace party_code = 1 if party_name == "Conservative"
replace party_code = 2 if party_name == "Labour"
replace party_code = 3 if party_name == "Alliance"
replace party_code = 4 if party_name == "SNP/Plaid Cymru"
replace party_code = -9999 if region == "Northern Ireland"
replace party_code = 9 if party_code > 4
 
* Generate rank of parties

gsort constituency_name -votes
bysort constituency_name : gen rank = _n

gen first = party_name if rank == 1
gen second = party_name if rank == 2
gen third = party_name if rank == 3

bysort constituency_name : gen winner83 = first[1]
bysort constituency_name : gen runnerup83 = second[2]
bysort constituency_name : gen thirdplace83 = third[3]

* Generate margins of victory

bysort constituency_name : gen margin = (share[1] - share[2])*100

* Generate effective number of electoral parties

gen sqrvotepr = (share)^2

bysort constituency_name : egen sumsqrvotepr = sum(sqrvotepr)

gen enep = 1/sumsqrvotepr

* Labelling parties

replace party_name = "1" if party_name == "Conservative"
replace party_name = "2" if party_name == "Labour"
replace party_name = "3" if party_name == "Alliance"
replace party_name = "4" if party_name == "SNP/Plaid Cymru"
replace party_name = "9" if party_name!="1" & party_name!="2" & party_name!="3" & party_name!="4"

destring (party_name), replace

replace winner83 = "1" if winner83 == "Conservative"
replace winner83 = "2" if winner83 == "Labour"
replace winner83 = "3" if winner83 == "Alliance"
replace winner83 = "4" if winner83 == "SNP/Plaid Cymru"
replace winner83 = "9" if winner83!="1" & winner83!="2" & winner83!="3" & winner83!="4"

destring (winner83), replace

replace runnerup83 = "1" if runnerup83 == "Conservative"
replace runnerup83 = "2" if runnerup83 == "Labour"
replace runnerup83 = "3" if runnerup83 == "Alliance"
replace runnerup83 = "4" if runnerup83 == "SNP/Plaid Cymru"
replace runnerup83 = "9" if runnerup83!="1" & runnerup83!="2" & runnerup83!="3" & runnerup83!="4"

destring (runnerup83), replace

replace thirdplace83 = "1" if thirdplace83 == "Conservative"
replace thirdplace83 = "2" if thirdplace83 == "Labour"
replace thirdplace83 = "3" if thirdplace83 == "Alliance"
replace thirdplace83 = "4" if thirdplace83 == "SNP/Plaid Cymru"
replace thirdplace83 = "9" if thirdplace83!="1" & thirdplace83!="2" & thirdplace83!="3" & thirdplace83!="4"

destring (thirdplace83), replace

label define party 1 "Conservative" 2 "Labour" 3 "Alliance" 4 "SNP/Plaid Cymru" 9 "Other"
label values party_name winner83 runnerup83 thirdplace83 party
 
* Selecting, ordering, and sorting variables

keep id constituency_name party_name rank votes share margin enep winner83 runnerup83 thirdplace83

order id constituency_name party_name rank votes share margin enep winner83 runnerup83 thirdplace83

sort id rank

drop if rank > 3

* Drop duplicates

duplicates tag id, gen(dup)
duplicates drop id, force
drop dup

* Saving file

save UK83_results, replace

log close